﻿CREATE PROCEDURE [dbo].[usp_select_Messages]
(
      @FaultID varchar(38)   = NULL
	 ,@MessageID varchar(38) = NULL
	 ,@Debug bit			 = 0
)
AS

SET NOCOUNT ON;

-- Bootstrap
DECLARE @DebugPrefix       varchar(10);
DECLARE @ErrorCode         int;

SET @ErrorCode          = 0;
SET @DebugPrefix        = '>>> DEBUG: ';


-- Test harness
/*
-- declare the stored procedure paramters

DELCARE @PARAM1;
DECLARE @PARAM2;
-- ...
DECLARE @PARAMn

-- now set them
SELECT
	 @PARAM1  = 'value_1'
	,@PARAM2  = 'value_2'
-- ...
   ,@Debug                 = 1;

*/

-- Print parameters
IF (@Debug > 0)
BEGIN
	PRINT @DebugPrefix + '@FaultID = ' + @FaultID
    PRINT @DebugPrefix + '@MessageID = ' + @MessageID
END;

SELECT
	M.MessageID,
	M.NativeMessageID,
	M.FaultID,
	M.ContentType,
	M.MessageName,
	M.InterchangeID,
	M.RoutingUrl,
	MD.MessageData,
	M.ResubmitAttempted,
	M.ResubmitSuccessful,
	M.InsertedDate
	
FROM
	dbo.Message M
	INNER JOIN dbo.MessageData MD ON M.MessageID = MD.MessageID

WHERE
	M.MessageID = ISNULL(@MessageID, M.MessageID)
	AND M.FaultID = ISNULL(@FaultID, FaultID)


-- CHECK FOR ERROR
SET @ErrorCode = @@Error;
IF (@ErrorCode <> 0) GOTO ERROR_HANDLER;
			
RETURN 0;

-- Handles errors.
ERROR_HANDLER:
IF (@ErrorCode <> 0)
BEGIN
    IF (@Debug > 0) 
		BEGIN
			PRINT @DebugPrefix + 'Error Code = ' + CAST(@ErrorCode AS varchar(10)) + ' occurred.';
			RETURN @ErrorCode
		END

    -- Don't return any evidence of what specific error occurred, i.e. 
    -- we want to ward off potential hackers.
    RETURN 1;
END;
